﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Extend.Models;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Estsh.Core.Wms.Repositories
{
    public class OpenControlRepository : BaseRepository<BaseEntity>, IOpenControlRepository
    {
        public OpenControlRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        public bool AddOrderCheck(int orderid, int pdlineid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                List<SysSpotCheck> spotChecks= GetSpotChecks(pdlineid);
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();

                for (int i = 0; i < spotChecks.Count; i++)
                {
                    SysSpotCheck check = spotChecks[i];
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.sys_order_check(order_id,check_id,check_element,check_content,check_status,check_result) ");
                    SqlStringBuilder.Append("     VALUES(@orderId,@checkId,@checkElement,@checkContent,@checkStatus,@checkResult) ");
                    sqlLists.Add(SqlStringBuilder.ToString());

                    DynamicParameters dynamic = new DynamicParameters();
                    dynamic.Add("@orderId", orderid);
                    dynamic.Add("@checkId", check.CheckId);
                    dynamic.Add("@checkElement", check.CheckElement);
                    dynamic.Add("@checkContent", check.CheckContent);
                    dynamic.Add("@checkStatus", 10);
                    dynamic.Add("@checkResult", 1);
                    parameters.Add(dynamic);
                }
                bool result = this.ExecuteTransaction(sqlLists, parameters);
                return result;

            }
        }

        public List<SysOrderCheck> GetOrderChecks(int id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT a.*,b.part_no FROM dbo.sys_order_check a INNER JOIN dbo.mes_work_order (nolock) b ON a.order_id=b.ruid WHERE a.order_id={id} and b.enabled='Y'");

                return dbConn.Query<SysOrderCheck>(sql.ToString()).ToList();
            }
        }

        public List<SysOrderCheck> GetSysOrderChecks(string orderid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.sys_order_check WHERE order_id={orderid} ");

                return dbConn.Query<SysOrderCheck>(sql.ToString()).ToList();
            }
        }

        public List<MesWorkOrder_Extend> GetOrders(int id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE ruid={id} and enabled='Y'");

                return dbConn.Query<MesWorkOrder_Extend>(sql.ToString()).ToList();
            }
        }

        public List<MesWorkOrder_Extend> GetPartNoStatus(int id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder stringBuilder= new StringBuilder(1024);
                stringBuilder.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE ruid={id} and check_status in (20,30) and enabled='Y'");
                List<MesWorkOrder_Extend> orders = dbConn.Query<MesWorkOrder_Extend>(stringBuilder.ToString()).ToList();
                if (orders.Count > 0)
                {
                    return new List<MesWorkOrder_Extend>();
                }
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE part_no = (SELECT part_no FROM dbo.mes_work_order WHERE ruid={id})  AND check_status IN (20,30) and enabled='Y'");

                return dbConn.Query<MesWorkOrder_Extend>(sql.ToString()).ToList();
            }
        }

        public List<Syspdline_Extend> GetPdlineList(int loginid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT pd.pdline_code,MAX(pd.pdline_name) AS pdline_name,MAX(pd.pdline_id) AS pdline_id " +
                    "FROM dbo.sys_pdline pd (NOLOCK) INNER JOIN dbo.mes_work_order AS wo (NOLOCK) ON wo.pdline_code = pd.pdline_code" +
                    " WHERE pd.pdline_name IN  (SELECT A.name  FROM dbo.sys_web_menu AS A (NOLOCK)" +
                    "   INNER JOIN dbo.sys_role_privliege AS B (NOLOCK)  ON CAST(A.menu_id AS NVARCHAR) = B.fun_name" +
                    "    INNER JOIN sys_role_emp AS D (NOLOCK)  ON D.role_id = B.role_id" +
                    "     INNER JOIN sys_emp AS E (NOLOCK)  ON E.emp_id = D.emp_id   " +
                    $" WHERE E.emp_id = '{loginid}'  AND A.parent_id = 60    AND A.sort_num > 0   " +
                    $" AND A.name != 'PDA'  AND A.enabled = 'Y'   )  AND pd.enabled = 'Y'  AND wo.order_status=20  GROUP BY pd.pdline_code");

                return dbConn.Query<Syspdline_Extend>(sql.ToString()).ToList();
            }
        }

        public List<SysSpotCheck> GetSpotChecks(int id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT a.* FROM dbo.sys_spot_check (nolock) a left JOIN dbo.sys_line_check (nolock) b ON b.check_id = a.check_id WHERE b.pdline_id={id} and a.enabled='Y' and b.enabled='Y'");

                return dbConn.Query<SysSpotCheck>(sql.ToString()).ToList();
            }
        }

        public List<MesWorkOrder> GetWorkOrder(int id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE pdline_id={id} and check_status!=40 and enabled='Y' ORDER BY  produce_time ");

                return dbConn.Query<MesWorkOrder>(sql.ToString()).ToList();
            }
        }

        public int UpdateCheckStatus(int id,string time)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"UPDATE dbo.mes_work_order SET check_status=40,check_time='{time}',order_status=30 WHERE ruid={id} ");

                return dbConn.Execute(sql.ToString());
            }
        }

        public int UpdateCheckStatus(int orderid, int checkid,int status, int pdlineid,int loginid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder stringBuilder3= new StringBuilder(1024);
                stringBuilder3.Append($"SELECT * FROM dbo.sys_spot_check where check_id={checkid} and enabled='Y'");
                List<SysSpotCheck> spotChecks = dbConn.Query<SysSpotCheck>(stringBuilder3.ToString()).ToList();

                StringBuilder stringBuilder2 = new StringBuilder(1024);
                stringBuilder2.Append($"SELECT * FROM dbo.sys_emp WHERE emp_id={loginid} and enabled='Y'");
                List<SysEmp> emps = dbConn.Query<SysEmp>(stringBuilder2.ToString()).ToList();

                StringBuilder log = new StringBuilder(1024);
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parameter = new List<DynamicParameters>();
                log.Append("INSERT INTO dbo.sys_spot_check_log (pdline_id,order_id,check_element,check_content,check_result,check_person,check_time)");
                log.Append(" VALUES (@pdlineId,@orderId,@checkElement,@checkContent,@checkResult,@checkPerson,@checkTime)");
                sqlList.Add(log.ToString());
                DynamicParameters dynamic1 = new DynamicParameters();
                dynamic1.Add("@pdlineId", pdlineid);
                dynamic1.Add("@orderId", orderid);
                dynamic1.Add("@checkElement", spotChecks.Count() > 0 ? spotChecks.FirstOrDefault().CheckElement : "");
                dynamic1.Add("@checkContent", spotChecks.Count() > 0 ? spotChecks.FirstOrDefault().CheckContent : "");
                dynamic1.Add("@checkResult", status == 20 ? 2 : 3);
                dynamic1.Add("@checkPerson", emps.Count() > 0 ? emps.FirstOrDefault().EmpName : "");
                dynamic1.Add("@checkTime", DateTime.Now);
                parameter.Add(dynamic1);
                ExecuteTransaction(sqlList, parameter);

                StringBuilder sql = new StringBuilder(1024);
                if (status == 20)
                {
                    sql.Append($"UPDATE dbo.sys_order_check SET check_result=2 WHERE order_id={orderid} AND check_id={checkid};");
                    List<SysOrderCheck> orderChecks= GetSysOrderChecks(orderid.ToString());
                    if (orderChecks.Where(p => p.CheckStatus == 30).Count() <= 0)
                    {
                        sql.Append($"UPDATE dbo.sys_order_check SET check_status=20 WHERE order_id={orderid};");
                        sql.Append($"UPDATE dbo.mes_work_order SET check_status=20 WHERE ruid={orderid};");
                    }
                    dbConn.Execute(sql.ToString());
                    StringBuilder builder1 = new StringBuilder(1024);
                    builder1.Append($"SELECT * FROM dbo.sys_order_check (nolock) WHERE order_id={orderid} AND check_result=2 and enabled='Y'");
                    List<SysOrderCheck> sysOrderChecks = dbConn.Query<SysOrderCheck>(builder1.ToString()).ToList();
                    StringBuilder stringBuilder = new StringBuilder(1024);
                    stringBuilder.Append($"SELECT * FROM dbo.sys_spot_check (nolock) a left JOIN dbo.sys_line_check b ON b.check_id = a.check_id WHERE b.pdline_id={pdlineid}  and a.enabled='Y' and b.enabled='Y'");
                    List<SysSpotCheck> sysSpotChecks = dbConn.Query<SysSpotCheck>(stringBuilder.ToString()).ToList();
                    if (sysOrderChecks.Count == sysSpotChecks.Count)
                    {
                        List<string> emails = sysSpotChecks.Select(p => p.CheckEmail).ToList();
                        string email=string.Join(",", emails);
                        StringBuilder builder = new StringBuilder(1024);
                        builder.Append($"DELETE FROM dbo.sys_order_check WHERE order_id={orderid} AND check_result=2");
                        dbConn.Query<SysOrderCheck>(builder.ToString());
                        UpdateCheckStatus(orderid, DateTime.Now.ToString("yyyy-MM-dd"));
                        StringBuilder stringBuilder1 = new StringBuilder(1024);
                        List<string> sqlLists = new List<string>();
                        List<DynamicParameters> parameters = new List<DynamicParameters>();
                        stringBuilder1.Append("INSERT INTO dbo.sys_email (title,check_content,email_type,operation_type,email_address,is_send,enabled,create_userid,create_time) ");
                        stringBuilder1.Append(" VALUES(@title,@checkContent,@emailType,@operationType,@emailAddress,@isSend,@enabled,@createUserid,@createTime)");
                        sqlLists.Add(stringBuilder1.ToString());
                        DynamicParameters dynamic = new DynamicParameters();
                        dynamic.Add("@title", "工单已开启，请知悉！");
                        dynamic.Add("@checkContent", "点检完成");
                        dynamic.Add("@emailType", "正常开启");
                        dynamic.Add("@operationType", "工单开启");
                        dynamic.Add("@emailAddress",email);
                        dynamic.Add("@isSend", 'N');
                        dynamic.Add("@enabled", 'Y');
                        dynamic.Add("@createUserid",loginid);
                        dynamic.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        parameters.Add(dynamic);
                        ExecuteTransaction(sqlLists, parameters);
                        return 1;
                    }
                    else
                    {
                        return 2;
                    }
                    
                }
                if (status == 30)
                {
                    sql.Append($"UPDATE dbo.sys_order_check SET check_result=3 WHERE order_id={orderid} AND check_id={checkid};");
                    sql.Append($"UPDATE dbo.sys_order_check SET check_status=30 WHERE order_id={orderid};");
                    sql.Append($"UPDATE dbo.mes_work_order SET check_status=30 WHERE ruid={orderid};");
                    dbConn.Execute(sql.ToString());

                }
                
                return 0;
            }
        }

        public int UpdateAgain(int orderid,int pdlineid,string loginid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder builder=new StringBuilder(1024);
                builder.Append($"SELECT * FROM dbo.sys_spot_check (nolock) a INNER JOIN dbo.sys_line_check (nolock) b ON b.check_id = a.check_id WHERE b.pdline_id={pdlineid}  and a.enabled='Y' and b.enabled='Y'");
                List<SysSpotCheck> spotChecks=dbConn.Query<SysSpotCheck>(builder.ToString()).ToList();
                List<string> emails = spotChecks.Select(p => p.CheckEmail).ToList();
                string email=string.Join(",", emails);
                StringBuilder stringBuilder1 = new StringBuilder(1024);
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                stringBuilder1.Append("INSERT INTO dbo.sys_email (title,check_content,email_type,operation_type,email_address,is_send,enabled,create_userid,create_time) ");
                stringBuilder1.Append(" VALUES(@title,@checkContent,@emailType,@operationType,@emailAddress,@isSend,@enabled,@createUserid,@createTime)");
                sqlLists.Add(stringBuilder1.ToString());
                DynamicParameters dynamic = new DynamicParameters();
                dynamic.Add("@title", "工单重新点检，请知悉！");
                dynamic.Add("@checkContent", "重新点检");
                dynamic.Add("@emailType", "重新点检");
                dynamic.Add("@operationType", "工单重新点检");
                dynamic.Add("@emailAddress", email);
                dynamic.Add("@isSend", 'N');
                dynamic.Add("@enabled", 'Y');
                dynamic.Add("@createUserid",loginid);
                dynamic.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                parameters.Add(dynamic);
                ExecuteTransaction(sqlLists, parameters);

                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"UPDATE dbo.sys_order_check SET check_status=10,check_result=1 where order_id={orderid}; ");
                sql.Append($"UPDATE dbo.mes_work_order SET check_status=10 where ruid={orderid}");
                return dbConn.Execute(sql.ToString());
            }
        }

        public int UpdateWorkOrder(int orderid,int pdlineid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder builder1 = new StringBuilder(1024);
                builder1.Append($"SELECT * FROM dbo.sys_order_check (nolock)  WHERE order_id={orderid} AND check_result=2 and enabled='Y'");
                List<SysOrderCheck> sysOrderChecks = dbConn.Query<SysOrderCheck>(builder1.ToString()).ToList();
                StringBuilder stringBuilder= new StringBuilder(1024);
                stringBuilder.Append($"SELECT * FROM dbo.sys_spot_check (nolock) a left JOIN dbo.sys_line_check b ON b.check_id = a.check_id WHERE b.pdline_id={pdlineid}  and a.enabled='Y' and b.enabled='Y'");
                List<SysSpotCheck> sysSpotChecks = dbConn.Query<SysSpotCheck>(stringBuilder.ToString()).ToList();
                if (sysOrderChecks.Count == sysSpotChecks.Count)
                {
                    StringBuilder builder=new StringBuilder(1024);
                    builder.Append($"DELETE FROM dbo.sys_order_check WHERE order_id={orderid} AND check_result=2");
                    dbConn.Query<SysOrderCheck>(builder.ToString());
                    UpdateCheckStatus(orderid, DateTime.Now.ToString("yyyy-MM-dd"));
                    
                    return 1;
                }
                else
                {
                    return 2;
                }

            }
        }

        public List<MesWorkOrder_Extend> GetCheckFinish(int pdlineid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE pdline_id={pdlineid} and check_status=40 and enabled='Y'");

                return dbConn.Query<MesWorkOrder_Extend>(sql.ToString()).ToList();
            }
        }

        public List<SysSpotCheckReason> GetCheckReasons(int checkid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.sys_spot_check_reason (nolock) WHERE spot_check_id={checkid} and enabled='Y'");

                return dbConn.Query<SysSpotCheckReason>(sql.ToString()).ToList();
            }
        }

        public int AddReasonLog(int reasonid, int checkid,string loginid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.sys_spot_check (nolock) where check_id={checkid} and enabled='Y'");
                List<SysSpotCheck> sysSpotChecks=dbConn.Query<SysSpotCheck>(sql.ToString()).ToList();

                StringBuilder stringBuilder= new StringBuilder(1024);
                stringBuilder.Append($"SELECT * FROM dbo.sys_spot_check_reason where id={reasonid} and enabled='Y'");
                List<SysSpotCheckReason> reasons=dbConn.Query<SysSpotCheckReason>(stringBuilder.ToString()).ToList();

                StringBuilder stringBuilder1 = new StringBuilder(1024);
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                stringBuilder1.Append("INSERT INTO dbo.sys_email (title,check_content,email_type,operation_type,email_address,is_send,enabled,create_userid,create_time) ");
                stringBuilder1.Append(" VALUES(@title,@checkContent,@emailType,@operationType,@emailAddress,@isSend,@enabled,@createUserid,@createTime)");
                sqlLists.Add(stringBuilder1.ToString());
                DynamicParameters dynamic = new DynamicParameters();
                dynamic.Add("@title", "工单点检未通过，请知悉！");
                dynamic.Add("@checkContent", reasons.Count()>0?reasons.FirstOrDefault().Reason:"点检不通过");
                dynamic.Add("@emailType", "未通过");
                dynamic.Add("@operationType", "工单点检未通过");
                dynamic.Add("@emailAddress", sysSpotChecks.Count>0?sysSpotChecks.FirstOrDefault().CheckEmail:" ");
                dynamic.Add("@isSend", 'N');
                dynamic.Add("@enabled", 'Y');
                dynamic.Add("@createUserid",loginid);
                dynamic.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                parameters.Add(dynamic);
                ExecuteTransaction(sqlLists, parameters);


                return dbConn.Execute(sql.ToString());
            }
        }

        public int GetPdlineMaxCount(int pdlineid,int orderid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.sys_pdline (nolock) where pdline_id={pdlineid}  and enabled='Y'");
                List<Syspdline_Extend> syspdlines = dbConn.Query<Syspdline_Extend>(sql.ToString()).ToList();

                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append($"SELECT * FROM  dbo.mes_work_order (nolock) WHERE check_status IN (20,30) AND pdline_id={pdlineid}  and enabled='Y'");
                List<MesWorkOrder_Extend> mesWorkOrders = dbConn.Query<MesWorkOrder_Extend>(stringBuilder.ToString()).ToList();

                StringBuilder stringBuilder1= new StringBuilder(1024);
                stringBuilder1.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE ruid={orderid} and check_status in (20,30) and enabled='Y'");
                List<MesWorkOrder_Extend> orders = dbConn.Query<MesWorkOrder_Extend>(stringBuilder1.ToString()).ToList();
                if (orders.Count > 0)
                    return 1;

                if (mesWorkOrders.Count < syspdlines.FirstOrDefault().MaxCheckCount)
                {
                    return 1;
                }
                else
                    return 2;
            }
        }

        public List<MesWorkOrder_Extend> GetCheckOk(int orderid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append($"SELECT * FROM dbo.mes_work_order (nolock) WHERE part_no IN (SELECT part_no FROM dbo.mes_work_order (nolock) WHERE ruid={orderid} and enabled='Y') AND DATEDIFF(dd,check_time,GETDATE())=0 AND check_status=40 and enabled='Y'");

                return dbConn.Query<MesWorkOrder_Extend>(sql.ToString()).ToList();
            }
        }
    }
}
